/**
 * 
 */
package com.jm.web.data;

import java.sql.ResultSet;
import java.util.Enumeration;
import java.util.Vector;

import com.jm.utils.StringBuilderEx;
import com.jm.utils.StringHelper;
import com.jm.utils.Utils;

/**
 * @author yanjie
 * Feb 7, 2012 9:46:04 AM
 */
public class CrudDataEntity extends BaseDataEntity{
	public static final String TAG_DELETE_BEFORE_SQL = "tag_delete_before_sql";
	public static final String TAG_DELETE_AFTER_SQL = "tag_delete_after_sql";
	Utils utils = null;
	String key = "";
	String datasource = "";
	String table = "" ;
	Vector noQuotes = null;//不需要加單引號的欄位
	boolean bDebug = false;//調試模式 會打印出sql 語句等
	boolean bExecute = true;
	boolean bForceInsert = false;
	String getSelfSql = "";
	boolean bGetLastRecord = false;//是否要取出最新的一笔资料
	boolean bGetLastKey = false;//是否要取出最新的一笔资料
	boolean bSpportSimple = false;//是否支持簡體
	
	public CrudDataEntity Clone(){
		CrudDataEntity crud = new CrudDataEntity(utils, this.getDatasource(), this.getTable(), this.getKey());
		this.CopyTo(crud, false);
		return crud;
	}

	/**
	 * 構造函數
	 */
	public CrudDataEntity(Utils utils,String dataSource, String table, String key) {
		this.setUtils(utils);
		this.setDatasource(dataSource);
		this.setTable(table);
		this.setKey(key);
	}
	
	/**
	 * @return
	 * @throws Exception
	 * 自動保存
	 */
	public int save() throws Exception{
		if(this.isInsert()){
			return this.insert();
		}else{
			return this.update();
		}
	}
	
	/**
	 * @return
	 * @throws Exception
	 * 更新
	 */
	public int update() throws Exception{
		return update(null);
	}
	
	public int update(String params) throws Exception{
		if(StringHelper.IsNullOrEmpty(this.getKey()) || !this.hasKeyValue()){
			throw new Exception("更新時,主鍵值不可以為空!");
		}
		
		if(this.getParamList() == null || this.getParamList().size() < 1){
			throw new Exception("無更新的欄位!");
		}
		
		StringBuilderEx sql = new StringBuilderEx();
		sql.Append("update ");
		sql.Append(this.getTable());
		sql.Append(" set ");
		
		boolean bFirst = true;
		if(!StringHelper.IsNullOrEmpty(params)){
			for (int i = 0; i < params.split(",").length; i++) {
				String objKey = params.split(",")[i];
				if(this.getKey().equalsIgnoreCase(objKey)){
					continue;
				}
				
				if(!bFirst){
					sql.Append(" ,");
				}else{
					bFirst = false;
				}
				String colvalue = this.gpsv(objKey).replaceAll("[']","''");
				if(this.isNoQuotes(objKey,colvalue)){
					sql.Append(" %1$s = %2$s",objKey.toLowerCase(),colvalue);
				}else{
					if(this.isSpportSimple()){
						sql.Append(" [%1$s] = N'%2$s'",objKey.toLowerCase(),colvalue);
					}else{
						sql.Append(" [%1$s] = '%2$s'",objKey.toLowerCase(),colvalue);
					}
				}
			}
		}else{
			Enumeration keys = this.getParamList().keys();
			while (keys.hasMoreElements())
			{
				String objKey = (String)keys.nextElement();
				if(this.getKey().equalsIgnoreCase(objKey)){
					continue;
				}
				
				if(!bFirst){
					sql.Append(" ,");
				}else{
					bFirst = false;
				}
				String colvalue = this.gpsv(objKey).replaceAll("[']","''");
				if(this.isNoQuotes(objKey,colvalue)){
					sql.Append(" %1$s = %2$s",objKey.toLowerCase(),colvalue);
				}else{
					if(this.isSpportSimple()){
						sql.Append(" [%1$s] = N'%2$s'",objKey.toLowerCase(),colvalue);
					}else{
						sql.Append(" [%1$s] = '%2$s'",objKey.toLowerCase(),colvalue);
					}
				}
			}
		}
		
		sql.Append(" where [%1$s] = '%2$s' ",this.getKey(),this.gpsv(this.getKey()));
		
		if(this.isDebug()){
			System.out.println("dataSource:"+this.getDatasource());
			System.out.println("sql:"+sql.toString());
		}
		
		int saveCode = 0;
		if(this.isExecute()){
			saveCode = this.utils.executeUpdate(this.getDatasource(),sql.toString());
		}
		if(this.isGetLastRecord()){
			if(this.isDebug()){
				System.out.println("getSelfSql:" + this.getSelfSql());;
			}
			this.getSelf();
		}
		return saveCode;
	}
	
	public void getSelf() throws Exception{
		this.getSelf(null);
	}
	
	public void getSelf(String strKeyValue) throws Exception{
		if(!StringHelper.IsNullOrEmpty(strKeyValue))this.SetParamValue(this.getKey(),strKeyValue);
		
		if(this.hasKeyValue()){
			this.getSelfSql = "select * from "+this.getTable()+" where "+this.getKey()+" = '"+this.gpsv(this.getKey())+"'";
		}
		
		if(!StringHelper.IsNullOrEmpty(this.getSelfSql())){
			ResultSet rs = utils.executeQuery(this.getDatasource(),this.getSelfSql());
			if(rs.next())
				this.FromResultSet(rs,false);
		}
	}
	
	public void getSelf2(String strparams) throws Exception{
		StringBuilderEx sql = new StringBuilderEx();
		sql.Append("select top 1 * from ");
		sql.Append(this.getTable());
		sql.Append(" where  ");
		boolean bFirst = true;
		if(StringHelper.IsNullOrEmpty(strparams)){
			Enumeration keys = this.getParamList().keys();
			while (keys.hasMoreElements())
			{
				String objKey = (String)keys.nextElement();
				if(!bFirst){
					sql.Append(" and");
				}else{
					bFirst = false;
				}
				String colvalue = this.gpsv(objKey).replaceAll("[']","''");
				
				if(this.getKey().equalsIgnoreCase(objKey) && StringHelper.IsNullOrEmpty(colvalue)){
					continue;
				}
				
				if(this.isNoQuotes(objKey,colvalue)){
					sql.Append(" %1$s = %2$s",objKey.toLowerCase(),colvalue);
				}else{
					if(this.isSpportSimple()){
						sql.Append(" [%1$s] = N'%2$s'",objKey.toLowerCase(),colvalue);
					}else{
						sql.Append(" [%1$s] = '%2$s'",objKey.toLowerCase(),colvalue);
					}
				}
			}
			
			if(this.hasKeyValue()){
				if(!bFirst){
					sql.Append(" and");
				}else{
					bFirst = false;
				}
				
				if(this.isNoQuotes(this.getKey(),this.gpsv(this.getKey()))){
					sql.Append(" %1$s = %2$s",this.getKey(),this.gpsv(this.getKey()));
				}else{
					if(this.isSpportSimple()){
						sql.Append(" [%1$s] = N'%2$s'",this.getKey(),this.gpsv(this.getKey()));
					}else{
						sql.Append(" [%1$s] = '%2$s'",this.getKey(),this.gpsv(this.getKey()));
					}
				}
			}
		}else{
			String columns[] = strparams.split(",");
			for (int i = 0; i < columns.length; i++) {
				String objKey = columns[i];
				if(!bFirst){
					sql.Append(" and");
				}else{
					bFirst = false;
				}
				String colvalue = this.gpsv(objKey).replaceAll("[']","''");
				if(this.isNoQuotes(objKey,colvalue)){
					sql.Append(" %1$s = %2$s",objKey.toLowerCase(),colvalue);
				}else{
					if(this.isSpportSimple()){
						sql.Append(" [%1$s] = N'%2$s'",objKey.toLowerCase(),colvalue);
					}else{
						sql.Append(" [%1$s] = '%2$s'",objKey.toLowerCase(),colvalue);
					}
				}
			}
			if(this.hasKeyValue()){
				if(!bFirst){
					sql.Append(" and");
				}else{
					bFirst = false;
				}
				
				if(this.isNoQuotes(this.getKey(),this.gpsv(this.getKey()))){
					sql.Append(" %1$s = %2$s",this.getKey(),this.gpsv(this.getKey()));
				}else{
					if(this.isSpportSimple()){
						sql.Append(" [%1$s] = N'%2$s'",this.getKey(),this.gpsv(this.getKey()));
					}else{
						sql.Append(" [%1$s] = '%2$s'",this.getKey(),this.gpsv(this.getKey()));
					}
				}
			}
		}
		
		if(this.isDebug()){
			System.out.println("dataSource:"+this.getDatasource());
			System.out.println("sql:"+sql.toString());
		}
		
		ResultSet rs = utils.executeQuery(this.getDatasource(),sql.toString());
		if(rs.next())
			this.FromResultSet(rs,false);
	}
	
	public boolean hasKeyValue(){
		return !this.isNullOrEmpty(this.getKey()) && !"0".equals(this.getKeyValue());
	}
	/**
	 * @return
	 * @throws Exception
	 * 刪除
	 */
	public int delete() throws Exception{
		if(!this.hasKeyValue()){
			throw new Exception("沒有主鍵值,無法刪除!");
		}
		StringBuilderEx sql = new StringBuilderEx();
		if(!this.isNullOrEmpty(TAG_DELETE_BEFORE_SQL)){
			sql.Append("\r\n ");
			sql.Append(this.getDeleteBeforeSql());
		}
		
		sql.Append("\r\n delete from ");
		sql.Append(this.getTable());
		sql.Append(" where [%1$s] = '%2$s'",this.getKey(),this.gpsv(this.getKey()));
		
		if(!this.isNullOrEmpty(TAG_DELETE_AFTER_SQL)){
			sql.Append("\r\n ");
			sql.Append(this.getDeleteAfterSql());
		}
		
		int saveCode = 0;
		if(this.isExecute()){
			saveCode = this.utils.executeUpdate(this.getDatasource(),sql.toString());
		}
		
		if(this.isDebug()){
			System.out.println("dataSource:"+this.getDatasource());
			System.out.println("sql:"+sql.toString());
		}
		return saveCode;
	}
	
	public int cancel() throws Exception{
		if(!this.hasKeyValue()){
			throw new Exception("沒有主鍵值,無法取消!");
		}
		int cancelcode = 0;
		if(this.hasKeyValue()){
			StringBuilderEx sql = new StringBuilderEx();
			sql.Append("update ");
			sql.Append(this.getTable());
			sql.Append(" set ");
			sql.Append(" [enab] = '0' ");
			sql.Append(" where [%1$s] = '%2$s' ",this.getKey(),this.gpsv(this.getKey()));
			
			if(this.isDebug()){
				System.out.println("dataSource:"+this.getDatasource());
				System.out.println("sql:"+sql.toString());
			}
			
			if(this.isExecute()){
				cancelcode = this.utils.executeUpdate(this.getDatasource(),sql.toString());
			}
			return cancelcode;
		}
		return cancelcode;
	}
	
	public boolean isExist() throws Exception{
		return isExist(null);
	}
	public boolean isExist(String pcolums) throws Exception{
		if(this.getParamList() == null || this.getParamList().size()  < 1){
			return false;
		}
		
		StringBuilderEx sql = new StringBuilderEx();
		sql.Append("select top 1 * from ");
		sql.Append(this.getTable());
		sql.Append(" where  ");
		boolean bFirst = true;
		if(StringHelper.IsNullOrEmpty(pcolums)){
			Enumeration keys = this.getParamList().keys();
			while (keys.hasMoreElements())
			{
				String objKey = (String)keys.nextElement();
				if(!bFirst){
					sql.Append(" and");
				}else{
					bFirst = false;
				}
				String colvalue = this.gpsv(objKey).replaceAll("[']","''");
				
				if(this.getKey().equalsIgnoreCase(objKey) && StringHelper.IsNullOrEmpty(colvalue)){
					continue;
				}
				
				if(this.isNoQuotes(objKey,colvalue)){
					sql.Append(" %1$s = %2$s",objKey.toLowerCase(),colvalue);
				}else{
					if(this.isSpportSimple()){
						sql.Append(" [%1$s] = N'%2$s'",objKey.toLowerCase(),colvalue);
					}else{
						sql.Append(" [%1$s] = '%2$s'",objKey.toLowerCase(),colvalue);
					}
				}
			}
			
			if(this.hasKeyValue()){
				if(!bFirst){
					sql.Append(" and");
				}else{
					bFirst = false;
				}
				
				if(this.isNoQuotes(this.getKey(),this.gpsv(this.getKey()))){
					sql.Append(" %1$s <> %2$s",this.getKey(),this.gpsv(this.getKey()));
				}else{
					if(this.isSpportSimple()){
						sql.Append(" [%1$s] <> N'%2$s'",this.getKey(),this.gpsv(this.getKey()));
					}else{
						sql.Append(" [%1$s] <> '%2$s'",this.getKey(),this.gpsv(this.getKey()));
					}
				}
			}
		}else{
			String columns[] = pcolums.split(",");
			for (int i = 0; i < columns.length; i++) {
				String objKey = columns[i];
				if(!bFirst){
					sql.Append(" and");
				}else{
					bFirst = false;
				}
				String colvalue = this.gpsv(objKey).replaceAll("[']","''");
				if(this.isNoQuotes(objKey,colvalue)){
					sql.Append(" %1$s = %2$s",objKey.toLowerCase(),colvalue);
				}else{
					if(this.isSpportSimple()){
						sql.Append(" [%1$s] = N'%2$s'",objKey.toLowerCase(),colvalue);
					}else{
						sql.Append(" [%1$s] = '%2$s'",objKey.toLowerCase(),colvalue);
					}
				}
			}
			if(this.hasKeyValue()){
				if(!bFirst){
					sql.Append(" and");
				}else{
					bFirst = false;
				}
				
				if(this.isNoQuotes(this.getKey(),this.gpsv(this.getKey()))){
					sql.Append(" %1$s <> %2$s",this.getKey(),this.gpsv(this.getKey()));
				}else{
					if(this.isSpportSimple()){
						sql.Append(" [%1$s] <> N'%2$s'",this.getKey(),this.gpsv(this.getKey()));
					}else{
						sql.Append(" [%1$s] <> '%2$s'",this.getKey(),this.gpsv(this.getKey()));
					}
				}
			}
		}
		
		if(this.isDebug()){
			System.out.println("dataSource:"+this.getDatasource());
			System.out.println("sql:"+sql.toString());
		}
		
		ResultSet rs = this.utils.executeQuery(this.getDatasource(),sql.toString());
		boolean bexits = false;
		if(rs.next()){
			this.FromResultSet(rs, false);
			bexits = true;
		}
		return bexits;
	}
	
	/**
	 * @return
	 * @throws Exception
	 * 新增
	 */
	public int insert() throws Exception{
		if(this.getParamList() == null || this.getParamList().size()  < 1){
			throw new Exception("無新增的欄位!");
		}
		
		StringBuilderEx sql = new StringBuilderEx();
		sql.Append("insert into ");
		sql.Append(this.getTable());
		
		StringBuffer cols = new StringBuffer();
		StringBuffer values = new StringBuffer();
		
		Enumeration keys = this.getParamList().keys();
		boolean bFirst = true;
		String value = "" ;
		while (keys.hasMoreElements())
		{
			String objKey = (String)keys.nextElement();
			value = this.gpsv(objKey).replaceAll("[']","''");
			
//			if(this.getKey().equalsIgnoreCase(objKey) && StringHelper.IsNullOrEmpty(value)){
//				continue;
//			}
			
			if(this.getKey().equalsIgnoreCase(objKey)){
				continue;
			}
			
			
			if(!bFirst){
				cols.append(" ,");
				values.append(" ,");
			}else{
				bFirst = false;
			}
			cols.append("[").append(objKey.toLowerCase()).append("]");
			
			if(this.isNoQuotes(objKey,value)){
				values.append(value);
			}else{
				if(this.isSpportSimple()){
					values.append("N'").append(value).append("'");
				}else{
					values.append("'").append(value).append("'");
				}
			}
			
		}
		
		//如果主鍵值有對應的值,則也需要放到insert語句中去
		boolean bautokey = false;
		if(this.hasKeyValue()){
			cols.append(" ,");
			values.append(" ,");
			
			cols.append(this.getKey());
			values.append(this.gpsv(this.getKey()));
		}else{
			bautokey = true;
		}
		
		sql.Append(" (%1$s) ",cols.toString());
		sql.Append(" values(%1$s)",values.toString());
		
		
		if(bautokey){
			if(this.isGetLastKey()){
				sql.Append("\r\n declare @id nvarchar(50)");
				sql.Append("\r\n select  @id = scope_identity() ");
				sql.Append("\r\n select @id as "+this.getKey());
			}else if(this.isGetLastRecord()){
				sql.Append("\r\n declare @id nvarchar(50)");
				sql.Append("\r\n select  @id = scope_identity() ");
				sql.Append("\r\n select * from ");
				sql.Append(this.getTable());
				sql.Append(" where "+this.getKey()+" = @id");
			}
		}
		
		int saveCode = 0;
		if(this.isExecute()){
			if(this.isGetLastKey() || this.isGetLastRecord()){
				ResultSet rs = this.utils.executeQuery(this.getDatasource(),sql.toString());
				if(rs.next()){
					this.FromResultSet(rs,false);
					if(this.gpiv(this.getKey()) > 0){
						saveCode = 1;
					}
				}
			}else{
				saveCode = this.utils.executeUpdate(this.getDatasource(),sql.toString());
			}
		}
		
		if(this.isDebug()){
			System.out.println("dataSource:"+this.getDatasource());
			System.out.println("sql:"+sql.toString());
		}
		
		if(!bautokey && this.isGetLastRecord()){
			if(this.isDebug()){
				System.out.println("getSelfSql:" + this.getSelfSql());;
			}
			this.getSelf();
		}
		return saveCode;
	}
	
	
	
	public void appendSaveCols(){
		if(this.isInsert()){
			this.appendInserCols();
		}else{
			this.appendUpdateCols();
		}
	}
	
	public String getInsertCols(){
		return "prgName,crtDay,crtName,crtIp,updDay,updName,updIp";
	}
	
	public void appendInserCols(){
		this.appendCols(getInsertCols());
	}
	
	public void appendInserCols2(){
		this.appendCols("prgName,crtDay,crtName,crtIp");
	}
	
	public String getUpdateCols(){
		return "prgName,updDay,updName,updIp";
	}
	
	public void appendUpdateCols(){
		this.appendCols(getUpdateCols());
	}
	
	public void appendCols(String args){
		if(StringHelper.IsNullOrEmpty(args))return ;
		String colName = "";
		String colValue = "";
		for (int i = 0; i < args.split("[,]").length; i++) {
			colName = args.split("[,]")[i];
			colValue = ""; 
			if("prgName".equalsIgnoreCase(colName)){
				colValue = this.getUtils().getPage().getRequest().getRequestURI().toString();
			}else if("crtDay".equalsIgnoreCase(colName) || "updDay".equalsIgnoreCase(colName) || colName.endsWith("Day")){
				colValue = "getDate()";
			}else if("crtName".equalsIgnoreCase(colName) || "updName".equalsIgnoreCase(colName)){
				colValue = this.getUtils().getChName();
			}else if("crtIp".equalsIgnoreCase(colName) || "updIp".equalsIgnoreCase(colName)){
				colValue = this.getUtils().getRemoteAddr();
			}else if("crtHr006tsn".equalsIgnoreCase(colName) || "updHr006tsn".equalsIgnoreCase(colName)){
				colValue = this.getUtils().getHr006Tsn();
			}
			this.SetParamValue(colName,colValue);
		}
	}
	
	public boolean isNoQuotes(String colName,String colvalue){
		if("getdate()".equalsIgnoreCase(colvalue)){
			return true;
		}
		
		if(this.noQuotes == null || this.noQuotes.size() == 0)
			return false;
		
		return this.noQuotes.contains(colName);
	}
	
	public void addNoQuotesCol(String colName){
		if(this.noQuotes == null)
			this.noQuotes = new Vector();
		this.noQuotes.add(colName);
	}
	
	/**
	 * @return
	 * 是否新增
	 */
	public boolean isInsert(){
		if(this.isForceInsert())
			return true;
		return !this.hasKeyValue();
	}
	
	public Utils getUtils() {
		return utils;
	}

	public void setUtils(Utils utils) {
		this.utils = utils;
	}

	public String getKey() {
		return key;
	}
	
	public String getKeyValue(){
		return this.gpsv(key);
	}
	
	public void setKeyValue(String value) {
		this.spsv(key, value);
	}
	
	public void setKey(String key) {
		this.key = key;
	}

	public String getDatasource() {
		return datasource;
	}

	public void setDatasource(String datasource) {
		this.datasource = datasource;
	}

	public String getTable() {
		return table;
	}

	public void setTable(String table) {
		this.table = table;
	}

	public boolean isDebug() {
		return bDebug;
	}

	public void setDebug(boolean debug) {
		bDebug = debug;
	}

	public boolean isForceInsert() {
		return bForceInsert;
	}

	public void setForceInsert(boolean forceInsert) {
		bForceInsert = forceInsert;
	}

	public String getSelfSql() {
		return getSelfSql;
	}

	public void setGetSelfSql(String getSelfSql) {
		this.getSelfSql = getSelfSql;
	}
	
	public boolean isGetLastRecord() {
		return bGetLastRecord;
	}

	public void setGetLastRecord(boolean getLastRecord) {
		bGetLastRecord = getLastRecord;
	}
	
	public boolean isGetLastKey() {
		return bGetLastKey;
	}

	public void setGetLastKey(boolean bGetLastKey) {
		this.bGetLastKey = bGetLastKey;
	}
	
	public boolean isExecute() {
		return bExecute;
	}

	public void setExecute(boolean execute) {
		if(!execute){
			this.setDebug(true);
		}
		bExecute = execute;
	}
	
	public boolean isSpportSimple() {
		return bSpportSimple;
	}

	public void setSpportSimple(boolean bSpportSimple) {
		this.bSpportSimple = bSpportSimple;
	}
	
	public String getDeleteBeforeSql(){
		return this.gpsv(TAG_DELETE_BEFORE_SQL);
	}
	
	public String getDeleteAfterSql(){
		return this.gpsv(TAG_DELETE_AFTER_SQL);
	}

	public void setDeleteBeforeSql(String sql){
		this.spsv(TAG_DELETE_BEFORE_SQL, sql);
	}
	
	public void setDeleteAfterSql(String sql){
		this.spsv(TAG_DELETE_AFTER_SQL, sql);
	}
	
}
